﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace QuanLyNhaHang.DataAccess
{
    class SqlService
    {
        private static String _connectString = @"Data Source=.;Initial Catalog=DoAnQLNhaHang;Integrated Security=True";
        protected SqlConnection cn;
        protected SqlCommand command;
        protected SqlDataAdapter adapter;

        public SqlService()
        {
            cn = new SqlConnection(_connectString);
        }

        public static String CONNECTSTRING
        {
            get { return SqlService._connectString; }
            set { SqlService._connectString = value; }
        }
        //hellsofijsodifj

        public void connect()
        {
            if (cn.State == ConnectionState.Closed)
            {
                cn.Open();
            }
        }

        public void sayhello()
        {

        }

        public void disconnect()
        {
            if (cn.State == ConnectionState.Open)
            {
                cn.Close();
            }
        }

        public DataTable getDataTable(String sql)
        {
            this.connect();
            DataTable dt = new DataTable();
            adapter = new SqlDataAdapter(sql, cn);
            adapter.Fill(dt);
            this.disconnect();
            return dt;
        }

        public void executeNonQuery(String sql)
        {
            this.connect();
            command = new SqlCommand(sql, cn);
            command.ExecuteNonQuery();
            this.disconnect();
        }

        public Object executeScalar(String sql)
        {
            this.connect();
            command = new SqlCommand(sql, cn);
            object o = command.ExecuteScalar();
            this.disconnect();
            return o;
        }

        public int ExcuteStoredProcedure(String StoreName, SqlParameter[] param)
        {
            int count = 0;
            command = new SqlCommand(StoreName, cn);
            command.CommandType = CommandType.StoredProcedure;
            if (param != null)
            {
                foreach (SqlParameter i in param)
                {
                    command.Parameters.Add(i);
                }
            }

            try
            {
                this.connect();
                count = command.ExecuteNonQuery();
            }
            finally
            {
                this.disconnect();
            }
            return count;
        }

        public DataTable getDTStoredProcedure(String StoreName, SqlParameter[] param)
        {
            this.connect();
            command = new SqlCommand(StoreName,cn);
            command.CommandType = CommandType.StoredProcedure;
            if (param != null)
            {
                foreach (SqlParameter i in param)
                {
                    command.Parameters.Add(i);
                }
            }
            adapter = new SqlDataAdapter(command);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            this.disconnect();
            return dt;
        }

        public Object executeScalarSProcedure(String StoreName,SqlParameter[] param)
        {
            this.connect();
            command = new SqlCommand(StoreName, cn);
            command.CommandType = CommandType.StoredProcedure;
            if (param != null)
            {
                foreach (SqlParameter i in param)
                {
                    command.Parameters.Add(i);
                }
            }

            Object o = command.ExecuteScalar();
            this.disconnect();
            return o;
        }


    }
}
